package br.com.datasun.biblioteca.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import br.com.datasun.biblioteca.dao.factory.ConnectionFactory;
import br.com.datasun.biblioteca.entities.Biblioteca;
import br.com.datasun.biblioteca.entities.Endereco;
import br.com.datasun.biblioteca.entities.Pessoa;
import br.com.datasun.biblioteca.exceptions.DAOException;

public class PessoaDAO {

	public Pessoa inserir(Pessoa pessoa) throws DAOException{
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		ResultSet resultSet = null;
		Endereco endereco = pessoa.getEndereco();
		StringBuilder sql = new StringBuilder();
		sql.append("insert pessoa (nome, email, ativo, id_endereco) values (?, ?, ?, ?)");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, pessoa.getNome());
			preparedStatement.setString(2, pessoa.getEmail());
			preparedStatement.setBoolean(3, pessoa.isAtivo());
			preparedStatement.setLong(4,endereco.getId());
			preparedStatement.execute();
			resultSet = preparedStatement.getGeneratedKeys();

			if(resultSet.next()){
				Long idGerado = resultSet.getLong(1);
				pessoa.setId(idGerado);
			}
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(resultSet != null){
					resultSet.close();
				}
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return pessoa;		
	}
	
	public boolean atualizarPessoa(Pessoa pessoa) throws DAOException{
		boolean result = false;
		Connection connection = null;
		PreparedStatement preparedStatement   = null;
		Endereco endereco = pessoa.getEndereco();
		StringBuilder sql = new StringBuilder();
		sql.append("update pessoa          ");
		sql.append("   set nome        = ? ");
		sql.append("      ,email       = ? ");
		sql.append("      ,ativo       = ? ");
		sql.append("      ,id_endereco = ? ");
		sql.append(" where id = ?          ");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setString(1, pessoa.getNome());
			preparedStatement.setString(2, pessoa.getEmail());
			preparedStatement.setBoolean(3, pessoa.isAtivo());
			preparedStatement.setLong(4, endereco.getId());
			preparedStatement.setLong(5, pessoa.getId());
			preparedStatement.executeUpdate();
			result = true;
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return result;
	}

	public boolean excluirPessoa(Long id) throws DAOException{
		boolean result = false;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		StringBuilder sql = new StringBuilder();
		sql.append("delete from pessoa ");
		sql.append(" where id= ?");
		try{
			connection = ConnectionFactory.createConnectionToMySQL();
			preparedStatement = connection.prepareStatement(sql.toString());
			preparedStatement.setLong(1, id);
			preparedStatement.execute();
			result = true;
		}catch (Exception e) {
			throw new DAOException(e);
		}finally{
			try{
				if(preparedStatement != null){
					preparedStatement.close();
				}
				if(connection != null){
					connection.close();
				}
			}catch (Exception e) {
				throw new DAOException(e);
			}
		}
		return result;
	}	
	
}
